package com.geotmt.billingcenter.modules.statis.mapper.provider;

import com.alibaba.druid.util.StringUtils;
import com.geotmt.billingcenter.common.entry.CostStatisEntry;
import com.geotmt.billingcenter.common.vo.params.ParamsCost;
import org.apache.ibatis.annotations.Param;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.MessageFormat;
import java.util.Date;
import java.util.List;

/**
 * @Description 成本统计SQL
 * @Author      yanghanwei
 * @Mail        yanghanwei@geotmt.com
 * @Date        2019/4/1 16:14
 * @Version     v1
 **/
public class CostStatisProvider extends AbstractStatis{

    private static final Logger logger = LoggerFactory.getLogger(CostStatisProvider.class);

    /**
     * 消耗统计(最细维度)
     * @param obj
     * @return
     */
    public String costStatisList(ParamsCost obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t1.day_flag dayFlag,t1.product_name prodbatchInsertStatisuctName,t1.ds_name dsName,t1.company_name companyName,t1.cuser_name cuserName,t1.interface_name interfaceName,t1.isp isp,")
                .append(" t1.out_interface_name outInterfaceName,")
                .append(" count_query countQuery, count_get countGet, count_timeout countTimeout,count_charging countCharging,cost from (")
                .append("SELECT ")
                .append(this.preSqlCost(obj,"cost"))
                .append(" SUM(cost.count_query) count_query,SUM(cost.count_get) count_get,SUM(cost.count_timeout) count_timeout,SUM(cost.count_charging) count_charging,SUM(cost) cost")
                .append(" FROM fin_cost_statis cost")
                .append(this.joinSqlCost(obj,"cost"))
                .append(" WHERE cost.status=1")
                .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"cost"))
                .append(whereSqlCost(obj,"cost"))
                .append(groupSqlCost(obj,"cost"))
                .append(") t1");
        //不传任何参数
        if(StringUtils.isEmpty(obj.getProductCode()) && null == obj.getCompanyId() && null == obj.getCuserId()
            && null == obj.getInterfaceId() && null == obj.getOutInterfaceId() && null == obj.getDsId()){
            sql = new StringBuffer();
            sql.append("SELECT t1.day_flag dayFlag, t1.product_name productName, t1.ds_name dsName, t1.company_name companyName, t1.cuser_name cuserName, t1.interface_name interfaceName,")
                   .append(" t1.isp isp, t1.out_interface_name outInterfaceName, count_query countQuery, count_get countGet, count_timeout countTimeout, count_charging countCharging,cost")
                   .append(" FROM")
                   .append(" (SELECT day_flag, cost.product_code, product.product_name, cost.company_id, com.company_full_name company_name,")
                   .append(" cost.cuser_id, cuser.real_name cuser_name, cost.ds_id, ds.ds_full_name ds_name, cost.out_interface_id,")
                   .append(" out_inter.interface_name out_interface_name, cost.interface_id, inter.interface_name, cost.isp,")
                   .append(" cost.count_query count_query, cost.count_get count_get, cost.count_timeout count_timeout, cost.count_charging count_charging,cost.cost")
                   .append(" FROM fin_cost_statis cost")
                   .append(" LEFT JOIN t_product product ON cost.product_code = product.product_code")
                   .append(" LEFT JOIN t_company_info com  ON cost.company_id = com.id")
                   .append(" LEFT JOIN t_cuser_info cuser ON cost.cuser_id = cuser.id")
                   .append(" LEFT JOIN t_ds_info ds ON cost.ds_id = ds.id")
                   .append(" LEFT JOIN t_interface inter ON cost.interface_id = inter.id")
                   .append(" LEFT JOIN t_out_interface out_inter ON cost.out_interface_id = out_inter.id")
                   .append(" WHERE cost.status=1")
                   .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"cost"))
                   .append(" ) t1");
        }
        logger.info("成本统计(最细维度)SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 成本list的总计(最细力度)
     * @param obj
     * @return
     */
    public String costStatisTotal(ParamsCost obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t1.day_flag dayFlag, ")
                .append(outerSelectCost(obj,"t1"))
                .append(" t1.count_query, t1.count_get,t1.count_timeout,t1.count_charging, t1.cost")
                .append(" FROM")
                .append(" (SELECT day_flag, ")
                .append(innerSelectCost(obj))
                .append(" SUM(cost.count_query) count_query, SUM(cost.count_get) count_get, SUM(cost.count_timeout) count_timeout, SUM(cost.count_charging) count_charging,SUM(cost.cost) cost")
                .append(" FROM fin_cost_statis cost")
                .append(" LEFT JOIN t_product product ON cost.product_code = product.product_code")
                .append(" LEFT JOIN t_company_info com  ON cost.company_id = com.id")
                .append(" LEFT JOIN t_cuser_info cuser ON cost.cuser_id = cuser.id")
                .append(" LEFT JOIN t_ds_info ds ON cost.ds_id = ds.id")
                .append(" LEFT JOIN t_interface inter ON cost.interface_id = inter.id")
                .append(" LEFT JOIN t_out_interface out_inter ON cost.out_interface_id = out_inter.id")
                .append(" WHERE cost.status=1")
                .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"cost"))
                .append(whereSqlCost(obj,"cost"))
                .append(totalCostGroup(obj))
                .append(" ) t1");
        logger.info("成本list的总计(最细力度) SQL:",sql);
        return sql.toString();
    }

    /**
     * 成本历史的量统计
     * @param obj
     * @return
     */
    public String hisCostStatis(ParamsCost obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT #{cuserId} cuserId,#{outInterfaceId} interfaceId, SUM(t.count_charging) countCharging")
                .append(" FROM")
                .append(" (SELECT count_charging")
                .append(" FROM fin_cost_statis cost")
                .append(" WHERE cost.status=1  AND cost.cuser_id=#{cuserId} AND cost.out_interface_id=#{outInterfaceId}")
                .append(whereSql(obj.getEffectStartTime(),obj.getEffectEndTime(),"cost"))
                .append(" ) t");
        return sql.toString();
    }

    /**
     * 删除某天的成本统计信息
     * @param dayFlag
     * @return
     */
    public String deleteCostStatisForDay(@Param(value = "dayFlag")String dayFlag,
                                         @Param(value = "deleteTime") Date deleteTime){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE fin_cost_statis set status=2,delete_time=#{deleteTime} WHERE ");
        if(!StringUtils.isEmpty(dayFlag)){
            sql.append(" day_flag=#{dayFlag}");
        }else{
            sql.append(" 1=0");
        }
        return sql.toString();
    }

    /**
     * 删除某段时间的成本统计
     * @param startDay
     * @param endDay
     * @return
     */
    public String deleteCostStatisForDate(@Param(value = "startDay") String startDay,
                                          @Param(value = "endDay") String endDay){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE fin_cost_statis set status=2,delete_time=#{deleteTime} WHERE ");
        if(!StringUtils.isEmpty(startDay) && !StringUtils.isEmpty(endDay)){
            sql.append(" day_flag>=#{startDay} and day_flag<=#{endDay}");
        }else{
            sql.append(" 1=0");
        }
        return sql.toString();
    }

    public String updateCostStatis(@Param(value = "startDay") String startDay,
                                   @Param(value = "endDay") String endDay,
                                   @Param(value = "id") Integer id){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE `fin_cost_statis` a,")
                .append(" (SELECT tci.id cuser_id, tci.company_id, tp.`product_code`,ti.interface_id")
                .append(" FROM t_cuser_info tci")
                .append(" LEFT JOIN `t_customer_product` tcp ON tci.`company_id` = tcp.`customer_id`")
                .append(" LEFT JOIN t_product tp ON tcp.`product_id` = tp.`id`")
                .append(" LEFT JOIN t_cuser_interface ti ON ti.cuser_id = tci.id")
                .append(" ) b,")
                .append(" (SELECT toi.id,toi.ds_id FROM `t_out_interface` toi) c")
                .append(" SET")
                .append(" a.product_code = b.product_code,")
                .append(" a.`company_id` = b.company_id,")
                .append(" a.`ds_id`=c.ds_id,")
                .append(" a.`out_interface_id` = c.id,")
                .append(" a.`interface_id` = b.interface_id")
                .append(" WHERE a.status = 1");
        if(!StringUtils.isEmpty(startDay) && !StringUtils.isEmpty(endDay)){
            sql.append(" AND a.day_flag>=#{startDay} AND a.day_flag<=#{endDay}");
        }
        if(null != id){
            sql.append(" AND a.id=#{id}");
        }
        return sql.toString();
    }

    /**
     * 更新某天的金额
     * @param cuserId
     * @param outIfType
     * @param dayFlag
     * @param money
     * @return
     */
    public String updateCostMoney (@Param(value = "cuserId") Integer cuserId,
                                   @Param(value = "outIfType") String outIfType,
                                   @Param(value = "dayFlag") String dayFlag,
                                   @Param(value = "money") Double money){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE `fin_cost_statis` SET cost=#{money}")
                .append(" WHERE cuser_id=#{cuserId} AND status=1")
                .append(" AND out_interface_type=#{outIfType}")
                .append(" AND day_flag=#{dayFlag}");
        logger.info("更新某天的金额SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 批量插入统计数据
     * @return
     */
    public String batchInsertStatis(@Param(value = "subCostStatisList") List<CostStatisEntry> subCostStatisList){
        StringBuffer sql = new StringBuffer();
        StringBuffer batchSql = new StringBuffer();
        sql.append("INSERT INTO `fin_cost_statis`(cuser_id, interface_id, isp,count_query,count_get,count_timeout,count_charging,out_interface_type,day_flag,status) VALUES");
        if(null != subCostStatisList && !subCostStatisList.isEmpty()){
            int size = subCostStatisList.size();
            batchSql.append("(#'{'subCostStatisList[{0}].cuserId},#'{'subCostStatisList[{0}].outInterfaceId},#'{'subCostStatisList[{0}].isp}")
                    .append(",#'{'subCostStatisList[{0}].countQuery},#'{'subCostStatisList[{0}].countGet},#'{'subCostStatisList[{0}].countTimeout}")
                    .append(",#'{'subCostStatisList[{0}].countCharging},#'{'subCostStatisList[{0}].outInterfaceType},#'{'subCostStatisList[{0}].dayFlag}")
                    .append(",1)");
            MessageFormat mf = new MessageFormat(  batchSql.toString());
            for (int i = 0; i < size; i++) {
                sql.append(mf.format(new Object[]{i}));
                if (i < size - 1) {
                    sql.append(",");
                }
            }
        }
        logger.info("成本批量插入统计数据",sql);
        return sql.toString();
    }
}
